package jnpf.util;

import cn.hutool.core.codec.Base64;
import cn.hutool.core.util.ObjectUtil;
import com.google.common.collect.ImmutableMap;
import com.google.common.io.Files;
import jnpf.constant.MsgCode;
import jnpf.enums.ImageEnum;
import jnpf.enums.ResourceEnum;
import jnpf.enums.StyleTypeEnum;
import jnpf.exception.DataException;
import jnpf.univer.chart.UniverChartModel;
import jnpf.univer.data.resource.UniverDrawing;
import jnpf.univer.data.resource.UniverOffset;
import jnpf.univer.data.resource.UniverTransform;
import jnpf.univer.model.UniverWorkBook;
import jnpf.univer.properties.UniverProperties;
import jnpf.univer.resources.UniverResource;
import jnpf.univer.resources.UniverResourceData;
import jnpf.univer.sheet.UniverSheet;
import jnpf.univer.sheet.UniverSheetColumnData;
import jnpf.univer.sheet.UniverSheetRange;
import jnpf.univer.sheet.UniverSheetRowData;
import jnpf.univer.style.UniverStyle;
import jnpf.univer.style.UniverStyleBorder;
import jnpf.univer.style.UniverStyleTextRotation;
import jnpf.util.excel.ExcelParser;
import jnpf.util.excel.UniverHSSFExcel;
import jnpf.util.excel.UniverXSSFExcel;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.awt.Color;
import java.io.IOException;
import java.util.*;

/**
 * @author ：JNPF开发平台组
 * @version: V3.1.0
 * @copyright 引迈信息技术有限公司
 * @date ：2024/9/29 下午5:17
 */
@Slf4j
public class UniverExcel {

    private static Map<String, ExcelParser> excelMap = ImmutableMap.of(
            "xlsx", new UniverXSSFExcel(),
            "xls", new UniverHSSFExcel()
    );

    public static UniverWorkBook formFile(MultipartFile file) throws IOException {
        String fileName = file.getOriginalFilename();
        String type = Files.getFileExtension(fileName);
        ExcelParser excelParser = excelMap.get(type);
        if (excelParser == null) {
            throw new DataException(MsgCode.ETD110.get());
        }
        UniverWorkBook univerWorkBook = excelParser.formFile(file.getInputStream());
        return univerWorkBook;
    }


    public static void downExcel(String snapshot, List<UniverChartModel> chartList, XSSFWorkbook workbook, List<String> sheetList) {
        Map<UniverStyle, XSSFCellStyle> styleMap = new HashMap<>();
        UniverWorkBook univerWorkBook = JsonUtil.getJsonToBean(snapshot, UniverWorkBook.class);
        UniverResource univerResource = univerWorkBook.getResources().stream().filter(t -> ResourceEnum.SHEET_DRAWING_PLUGIN.name().equals(t.getName())).findFirst().orElse(null);
        Map<String, UniverResourceData> resourceMap = new HashMap<>();
        if (univerResource != null && ObjectUtil.isNotEmpty(univerResource.getData())) {
            Map<String, Object> data = JsonUtil.stringToMap(univerResource.getData());
            data.forEach((key, value) -> {
                resourceMap.put(key, JsonUtil.getJsonToBean(value, UniverResourceData.class));
            });
        }
        for (String sheetOrder : univerWorkBook.getSheetOrder()) {
            UniverSheet univerSheet = univerWorkBook.getSheets().get(sheetOrder);
            if (null != univerSheet) {
                if (!sheetList.contains(sheetOrder)) {
                    continue;
                }
                XSSFSheet sheet = workbook.createSheet(univerSheet.getName());
                workbook.setSheetHidden(workbook.getSheetIndex(sheet), Objects.equals(univerSheet.getHidden(), 1));
                Map<Integer, UniverSheetRowData> rowData = univerSheet.getRowData();
                Map<Integer, UniverSheetColumnData> colData = univerSheet.getColumnData();
                XSSFDrawing drawing = sheet.createDrawingPatriarch();
                //遍历数据
                univerSheet.getCellData().forEach((rowKey, sheetRow) -> {
                    XSSFRow row = sheet.createRow(rowKey);
                    UniverSheetRowData sheetRowData = rowData.get(rowKey);
                    if (null != sheetRowData) {
                        row.setZeroHeight(Objects.equals(sheetRowData.getHd(), 1));
                        if (null != sheetRowData.getH()) {
                            row.setHeightInPoints(sheetRowData.getH());
                        }
                    }
                    sheetRow.forEach((colKey, sheetCol) -> {
                        UniverSheetRange range = univerSheet.getMergeData().stream().filter(t -> Objects.equals(t.getStartRow(), rowKey) && Objects.equals(t.getStartColumn(), colKey)).findFirst().orElse(null);
                        XSSFCell cell = row.createCell(colKey);
                        UniverSheetColumnData columnData = colData.get(colKey);
                        if (null != columnData) {
                            sheet.setColumnWidth(colKey, (short) (columnData.getW() * 1.33 * 35));
                            sheet.setColumnHidden(colKey, Objects.equals(columnData.getHd(), 1));
                        }
                        if (null != sheetCol.getV()) {
                            if (Objects.equals(sheetCol.getT(), 2)) {
                                cell.setCellType(CellType.NUMERIC);
                                cell.setCellValue(Double.valueOf(sheetCol.getV().toString()));
                            } else if (Objects.equals(sheetCol.getT(), 3)) {
                                cell.setCellType(CellType.BOOLEAN);
                                cell.setCellValue(Boolean.valueOf(sheetCol.getV().toString()));
                            } else {
                                cell.setCellType(CellType.STRING);
                                String value = sheetCol.getV().toString();
                                if (ObjectUtil.isEmpty(value) && ObjectUtil.isNotEmpty(sheetCol.getP())) {
                                    UniverProperties properties = JsonUtil.getJsonToBean(sheetCol.getP(), UniverProperties.class);
                                    if (properties.getBody() != null) {
                                        value = properties.getBody().getDataStream();
                                    }
                                }
                                cell.setCellValue(value);
                            }
                        }
                        if (null != sheetCol.getF()) {
                            cell.setCellFormula(sheetCol.getF().toString().split("=")[1]);
                        }
                        if (null != sheetCol.getS()) {
                            Object s = sheetCol.getS();
                            UniverStyle basicStyle = univerWorkBook.getStyles().get(s);
                            if (null != basicStyle) {
                                UniverStyleBorder bd = basicStyle.getBd();
                                if (null != styleMap.get(basicStyle)) {
                                    bdAnchor(bd, rowKey, colKey, range, drawing);
                                    cell.setCellStyle(styleMap.get(basicStyle));
                                } else {
                                    XSSFCellStyle style = workbook.createCellStyle();
                                    if (null != basicStyle.getBg() && null != basicStyle.getBg().getRgb()) {
                                        String bgGrb = basicStyle.getBg().getRgb();
                                        XSSFColor color = color(bgGrb);
                                        if (color != null) {
                                            style.setFillForegroundColor(color);
                                            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                                        }
                                    }
                                    if (null != bd) {
                                        if (null != bd.getT() && null != bd.getT().getCl()) {
                                            String bdt = bd.getT().getCl().getRgb();
                                            if (ObjectUtil.isNotEmpty(bdt)) {
                                                XSSFColor color = color(bdt);
                                                if (color != null) {
                                                    style.setTopBorderColor(color);
                                                }
                                            }
                                            StyleTypeEnum bdStyle = StyleTypeEnum.getStyle(bd.getT().getS());
                                            if (ObjectUtil.isNotEmpty(bdStyle)) {
                                                style.setBorderTop(bdStyle.getBorderStyle());
                                            }
                                        }
                                        if (null != bd.getL() && null != bd.getL().getCl()) {
                                            String bdl = bd.getL().getCl().getRgb();
                                            if (ObjectUtil.isNotEmpty(bdl)) {
                                                XSSFColor color = color(bdl);
                                                if (color != null) {
                                                    style.setLeftBorderColor(color);
                                                }
                                            }
                                            StyleTypeEnum bdStyle = StyleTypeEnum.getStyle(bd.getL().getS());
                                            if (ObjectUtil.isNotEmpty(bdStyle)) {
                                                style.setBorderLeft(bdStyle.getBorderStyle());
                                            }
                                        }
                                        if (null != bd.getB() && null != bd.getB().getCl()) {
                                            String bdb = bd.getB().getCl().getRgb();
                                            if (ObjectUtil.isNotEmpty(bdb)) {
                                                XSSFColor color = color(bdb);
                                                if (color != null) {
                                                    style.setBottomBorderColor(color);
                                                }
                                            }
                                            StyleTypeEnum bdStyle = StyleTypeEnum.getStyle(bd.getB().getS());
                                            if (ObjectUtil.isNotEmpty(bdStyle)) {
                                                style.setBorderBottom(bdStyle.getBorderStyle());
                                            }
                                        }
                                        if (null != bd.getR() && null != bd.getR().getCl()) {
                                            String bdr = bd.getR().getCl().getRgb();
                                            if (ObjectUtil.isNotEmpty(bdr)) {
                                                XSSFColor color = color(bdr);
                                                if (color != null) {
                                                    style.setRightBorderColor(color);
                                                }
                                            }
                                            StyleTypeEnum bdStyle = StyleTypeEnum.getStyle(bd.getR().getS());
                                            if (ObjectUtil.isNotEmpty(bdStyle)) {
                                                style.setBorderRight(bdStyle.getBorderStyle());
                                            }
                                        }
                                        bdAnchor(bd, rowKey, colKey, range, drawing);
                                    }
                                    if (null != basicStyle.getHt()) {
                                        style.setAlignment(HorizontalAlignment.forInt(basicStyle.getHt()));
                                    }
                                    if (null != basicStyle.getVt()) {
                                        style.setVerticalAlignment(VerticalAlignment.forInt(basicStyle.getVt()));
                                    }
                                    if (null != basicStyle.getN() && null != basicStyle.getN().getPattern()) {
                                        DataFormat format = workbook.createDataFormat();
                                        style.setDataFormat(format.getFormat(basicStyle.getN().getPattern()));
                                    }
                                    if (null != basicStyle.getTb() && Objects.equals(basicStyle.getTb(), 3)) {
                                        style.setWrapText(true);
                                    }
                                    if (null != basicStyle.getTr()) {
                                        UniverStyleTextRotation tr = basicStyle.getTr();
                                        int v = tr.getV();
                                        int rotation = tr.getA();
                                        if (Objects.equals(v, 1)) {
                                            rotation = -255;
                                        }
                                        style.setRotation((short) -rotation);
                                    }
                                    XSSFFont font = workbook.createFont();
                                    if (null != basicStyle.getFf()) {
                                        font.setFontName(basicStyle.getFf());
                                    }
                                    if (null != basicStyle.getFs()) {
                                        font.setFontHeight(basicStyle.getFs());
                                    }
                                    if (null != basicStyle.getIt()) {
                                        font.setItalic(Objects.equals(basicStyle.getIt(), 1));
                                    }
                                    if (null != basicStyle.getBl()) {
                                        font.setBold(Objects.equals(basicStyle.getBl(), 1));
                                    }
                                    if (null != basicStyle.getCl() && null != basicStyle.getCl().getRgb()) {
                                        XSSFColor color = color(basicStyle.getCl().getRgb());
                                        if (color != null) {
                                            font.setColor(color);
                                        }
                                    }
                                    if (null != basicStyle.getUl() && null != basicStyle.getUl().getS()) {
                                        font.setUnderline(Objects.equals(basicStyle.getUl().getS(), 1) ? Font.U_SINGLE : Font.U_NONE);
                                    }
                                    if (null != basicStyle.getSt() && null != basicStyle.getSt().getS()) {
                                        font.setStrikeout(Objects.equals(basicStyle.getSt().getS(), 1));
                                    }
                                    style.setFont(font);
                                    cell.setCellStyle(style);
                                    styleMap.put(basicStyle, style);
                                }
                            }
                        }
                    });
                });
                //合并单元格
                for (UniverSheetRange region : new HashSet<>(univerSheet.getMergeData())) {
                    sheet.addMergedRegion(new CellRangeAddress(region.getStartRow(), region.getEndRow(), region.getStartColumn(), region.getEndColumn()));
                }
                //导出图片
                UniverResourceData resourceData = resourceMap.get(sheetOrder);
                if (null != resourceData) {
                    List<String> orderList = resourceData.getOrder();
                    Map<String, UniverDrawing> data = resourceData.getData();
                    for (String order : orderList) {
                        try {
                            UniverDrawing univerDrawing = data.get(order);
                            if (null != univerDrawing) {
                                String source = univerDrawing.getSource();
                                String imageType = univerDrawing.getImageSourceType();
                                byte[] bytes = null;
                                if (Objects.equals(ImageEnum.BASE64.name(), imageType)) {
                                    String chartSource = source.replace("data:image/png;base64,", "").replace("data:image/jpeg;base64,", "");
                                    bytes = Base64.decode(chartSource);
                                } else if (Objects.equals(ImageEnum.URL.name(), imageType)) {
                                    String[] split = source.split("=");
                                    String fileNameAll = DesUtil.aesDecode(split[split.length - 1]).replaceAll("\n", "");
                                    String[] fileData = fileNameAll.split("#");
                                    String fileName = fileData.length > 1 ? fileData[1] : "";
                                    String type = fileData.length > 2 ? fileData[2] : "";
                                    String typePath = FilePathUtil.getFilePath(type.toLowerCase());
                                    bytes = FileUploadUtils.downloadFileByte(typePath, fileName, false);
                                } else {
                                    UniverChartModel chartModel = chartList.stream().filter(t -> Objects.equals(t.getUnitId(), univerDrawing.getUnitId()) && Objects.equals(t.getSubUnitId(), univerDrawing.getSubUnitId()) && Objects.equals(t.getDrawingId(), univerDrawing.getDrawingId())).findFirst().orElse(null);
                                    if (chartModel != null && StringUtil.isNotEmpty(chartModel.getSource())) {
                                        String chartSource = chartModel.getSource().replace("data:image/png;base64,", "").replace("data:image/jpeg;base64,", "");
                                        bytes = Base64.decode(chartSource);
                                    }
                                }
                                if (bytes != null && bytes.length > 0) {
                                    UniverTransform sheetTransform = univerDrawing.getSheetTransform();
                                    UniverOffset from = sheetTransform.getFrom();
                                    UniverOffset to = sheetTransform.getTo();
                                    //图片导出
                                    XSSFClientAnchor anchor = new XSSFClientAnchor();
                                    anchor.setRow1(from.getRow());
                                    anchor.setCol1(from.getColumn());

                                    anchor.setRow2(to.getRow());
                                    anchor.setCol2(to.getColumn());

                                    anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_DO_RESIZE);
                                    int imageIndex = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                                    drawing.createPicture(anchor, imageIndex);
                                }
                            }
                        } catch (Exception e) {
                            log.error("图片导出失败:{}", e.getMessage());
                        }
                    }
                }
            }
        }
    }

    private static void bdAnchor(UniverStyleBorder bd, int rowKey, int colKey, UniverSheetRange range, XSSFDrawing drawing) {
        if (null != bd) {
            if (null != bd.getTl_br() && null != bd.getTl_br().getCl()) {
                int startRow = rowKey;
                int startCol = colKey;
                int endRow = rowKey + 1;
                int endCol = colKey + 1;
                if (range != null) {
                    endRow = range.getEndRow() + 1;
                    endCol = range.getEndColumn() + 1;
                }
                XSSFClientAnchor anchor = new XSSFClientAnchor();
                anchor.setRow1(startRow);
                anchor.setCol1(startCol);
                anchor.setRow2(endRow);
                anchor.setCol2(endCol);
                XSSFSimpleShape shape = drawing.createSimpleShape(anchor);
                StyleTypeEnum bdStyle = StyleTypeEnum.getStyle(bd.getTl_br().getS());
                if (ObjectUtil.isNotEmpty(bdStyle)) {
                    shape.setShapeType(bdStyle.getBorderStyle().getCode());
                }
                String bdr = bd.getTl_br().getCl().getRgb();
                if (ObjectUtil.isNotEmpty(bdr)) {
                    XSSFColor color = color(bdr);
                    if (color != null) {
                        shape.setLineStyleColor(color.getRGB()[0], color.getRGB()[1], color.getRGB()[2]);
                    }
                }
                shape.setShapeType(ShapeTypes.LINE);
            }
            if (null != bd.getTl_mr() && null != bd.getTl_mr().getCl()) {
                if (range != null) {
                    int startRow = rowKey;
                    int startCol = colKey;
                    int endRow = (rowKey + range.getEndRow() + 1) / 2;
                    int endCol = range.getEndColumn() + 1;
                    XSSFClientAnchor anchor = new XSSFClientAnchor();
                    anchor.setRow1(startRow);
                    anchor.setCol1(startCol);
                    anchor.setRow2(endRow);
                    anchor.setCol2(endCol);
                    XSSFSimpleShape shape = drawing.createSimpleShape(anchor);
                    StyleTypeEnum bdStyle = StyleTypeEnum.getStyle(bd.getTl_mr().getS());
                    if (ObjectUtil.isNotEmpty(bdStyle)) {
                        shape.setShapeType(bdStyle.getBorderStyle().getCode());
                    }
                    String bdr = bd.getTl_mr().getCl().getRgb();
                    if (ObjectUtil.isNotEmpty(bdr)) {
                        XSSFColor color = color(bdr);
                        if (color != null) {
                            shape.setLineStyleColor(color.getRGB()[0], color.getRGB()[1], color.getRGB()[2]);
                        }
                    }
                    shape.setShapeType(ShapeTypes.LINE);
                }
            }
            if (null != bd.getTl_bc() && null != bd.getTl_bc().getCl()) {
                if (range != null) {
                    int startRow = rowKey;
                    int startCol = colKey;
                    int endRow = range.getEndRow() + 1;
                    int endCol = (colKey + range.getEndColumn() + 1) / 2;
                    XSSFClientAnchor anchor = new XSSFClientAnchor();
                    anchor.setRow1(startRow);
                    anchor.setCol1(startCol);
                    anchor.setRow2(endRow);
                    anchor.setCol2(endCol);
                    XSSFSimpleShape shape = drawing.createSimpleShape(anchor);
                    StyleTypeEnum bdStyle = StyleTypeEnum.getStyle(bd.getTl_bc().getS());
                    if (ObjectUtil.isNotEmpty(bdStyle)) {
                        shape.setShapeType(bdStyle.getBorderStyle().getCode());
                    }
                    String bdr = bd.getTl_bc().getCl().getRgb();
                    if (ObjectUtil.isNotEmpty(bdr)) {
                        XSSFColor color = color(bdr);
                        if (color != null) {
                            shape.setLineStyleColor(color.getRGB()[0], color.getRGB()[1], color.getRGB()[2]);
                        }
                    }
                    shape.setShapeType(ShapeTypes.LINE);
                }
            }
            if (null != bd.getBl_tr() && null != bd.getBl_tr().getCl()) {
                int startRow = rowKey;
                int startCol = colKey;
                int endRow = rowKey + 1;
                int endCol = colKey + 1;
                if (range != null) {
                    endRow = range.getEndRow() + 1;
                    endCol = range.getEndColumn() + 1;
                }
                XSSFClientAnchor anchor = new XSSFClientAnchor();
                anchor.setRow1(startRow);
                anchor.setCol1(startCol);
                anchor.setRow2(endRow);
                anchor.setCol2(endCol);
                XSSFSimpleShape shape = drawing.createSimpleShape(anchor);
                StyleTypeEnum bdStyle = StyleTypeEnum.getStyle(bd.getBl_tr().getS());
                if (ObjectUtil.isNotEmpty(bdStyle)) {
                    shape.setShapeType(bdStyle.getBorderStyle().getCode());
                }
                String bdr = bd.getBl_tr().getCl().getRgb();
                if (ObjectUtil.isNotEmpty(bdr)) {
                    XSSFColor color = color(bdr);
                    if (color != null) {
                        shape.setLineStyleColor(color.getRGB()[0], color.getRGB()[1], color.getRGB()[2]);
                    }
                }
                shape.setShapeType(ShapeTypes.LINE_INV);
            }
            if (null != bd.getBc_tr() && null != bd.getBc_tr().getCl()) {
                if (range != null) {
                    int startRow = rowKey;
                    int startCol = range.getStartColumn();
                    int endRow = (rowKey + range.getEndRow() + 1) / 2;
                    int endCol = range.getEndColumn() + 1;
                    XSSFClientAnchor anchor = new XSSFClientAnchor();
                    anchor.setRow1(startRow);
                    anchor.setCol1(startCol);
                    anchor.setRow2(endRow);
                    anchor.setCol2(endCol);
                    XSSFSimpleShape shape = drawing.createSimpleShape(anchor);
                    StyleTypeEnum bdStyle = StyleTypeEnum.getStyle(bd.getBc_tr().getS());
                    if (ObjectUtil.isNotEmpty(bdStyle)) {
                        shape.setShapeType(bdStyle.getBorderStyle().getCode());
                    }
                    String bdr = bd.getBc_tr().getCl().getRgb();
                    if (ObjectUtil.isNotEmpty(bdr)) {
                        XSSFColor color = color(bdr);
                        if (color != null) {
                            shape.setLineStyleColor(color.getRGB()[0], color.getRGB()[1], color.getRGB()[2]);
                        }
                    }
                    shape.setShapeType(ShapeTypes.LINE_INV);
                }
            }
            if (null != bd.getMl_tr() && null != bd.getMl_tr().getCl()) {
                if (range != null) {
                    int startRow = rowKey;
                    int startCol = (colKey + range.getEndColumn() + 1) / 2;
                    int endRow = range.getEndRow() + 1;
                    int endCol = range.getEndColumn() + 1;
                    XSSFClientAnchor anchor = new XSSFClientAnchor();
                    anchor.setRow1(startRow);
                    anchor.setCol1(startCol);
                    anchor.setRow2(endRow);
                    anchor.setCol2(endCol);
                    XSSFSimpleShape shape = drawing.createSimpleShape(anchor);
                    StyleTypeEnum bdStyle = StyleTypeEnum.getStyle(bd.getMl_tr().getS());
                    if (ObjectUtil.isNotEmpty(bdStyle)) {
                        shape.setShapeType(bdStyle.getBorderStyle().getCode());
                    }
                    String bdr = bd.getMl_tr().getCl().getRgb();
                    if (ObjectUtil.isNotEmpty(bdr)) {
                        XSSFColor color = color(bdr);
                        if (color != null) {
                            shape.setLineStyleColor(color.getRGB()[0], color.getRGB()[1], color.getRGB()[2]);
                        }
                    }
                    shape.setShapeType(ShapeTypes.LINE_INV);
                }
            }
        }
    }

    private static XSSFColor color(String rgbCole) {
        byte[] rgb = null;
        try {
            Color color = new Color(Integer.parseInt(rgbCole.substring(1), 16));
            rgb = new byte[]{(byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue()};
        } catch (Exception e) {
            try {
                rgbCole = rgbCole.replaceAll("rgb\\(", "").replaceAll("\\)", "");
                String[] split = rgbCole.split(",");
                if (split.length >= 3) {
                    int red = Integer.parseInt(split[0]);
                    int green = Integer.parseInt(split[1]);
                    int blue = Integer.parseInt(split[2]);
                    rgb = new byte[]{(byte) red, (byte) green, (byte) blue};
                }
            } catch (Exception e1) {
            }
        }
        XSSFColor xssfColor = rgb == null ? null : new XSSFColor(rgb, new DefaultIndexedColorMap());
        return xssfColor;
    }
}
